<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2023/5/10
 * Time: 9:45
 */

namespace app\index\controller;
use app\index\model\CrmChannelCount;
use app\index\model\CrmCustomerIntegral;
use app\index\model\CrmDurations;
use app\index\model\CrmManage;
use app\index\model\CrmMedia;
use app\index\model\CrmCustomer;
use app\index\model\CrmMediaNext;
use app\index\model\CrmOrder;
use app\index\model\Goods;
use app\index\model\GoodsNext;
use app\index\model\CrmFenzu;
class Statistics extends Base
{

    /*
     * 渠道统计
     * */
    public function media(){
        $query = input();
        $query['limit'] = isset($query['limit'])?$query['limit']:10;
        $query['page'] = isset($query['page'])?$query['page']:1;
        if(isset($query['endtime']) && isset($query['starttime'])){
            if($query['starttime']>$query['endtime'])apiReturn(400,'查询开始日期不能大于结束日期');
        }
        if(empty($query['starttime'])|| empty($query['endtime'])){
            unset($query['starttime']);unset($query['endtime']);
        }
        $public_yingqi_where=[];
        if(isset($query['id'])){
            if(!empty($query['id'])){
                $public_yingqi_where['public_yingqi']=['in',$query['id']];
            }
            unset($query['id']);
        }else{
            $goods=(new Goods())->where('search_type','in',[1,2,3])->where('time','<',time())->page(1)->limit(2)->order('time desc')->select();
            $public_yingqi_where['public_yingqi']=['in',[$goods[0]['id'],$goods[1]['id']]];
        }
        $this->channel_media($query,$public_yingqi_where);
    }

    public function medias(){
        $query = input();
        $query['limit'] = isset($query['limit'])?$query['limit']:10;
        $query['page'] = isset($query['page'])?$query['page']:1;
        if(isset($query['endtime']) && isset($query['starttime'])){
            if($query['starttime']>$query['endtime'])apiReturn(400,'查询开始日期不能大于结束日期');
        }
        if(isset($query['id']) && !empty($query['id'])){
            $query['media_id']=$query['id'];
        }
        if(isset($query['id'])){
            unset($query['id']);
        }
        $this->channel_media($query);
    }

    private function channel_media($query,$public_yingqi_where=[]){
        $media = (new CrmMedia())->mediaList($query);
        $CrmCustomerM = new CrmCustomer;
        $media['total_amount']=0;
        $media['total_refund_amount']=0;
        $media['total_refund_rate']='0%';
        $media['total_conversion'] = 0;
        $media['total_conversion_rate']='0%';
        $media['wuxiao_amount'] = 0;
        $media['youxiao_amount'] = 0;
        $media['wuxiao_rate']='0%';
        $media['youxiao_rate']='0%';
        $media['total_money']=0;
        $list = [];
        foreach ($media['list'] as $key=>&$value){
//            $source_type_arr = array_column($value['media_next'],'type');
//            foreach ($source_type_arr as $key=>$type){
//                if($type===''){
//                    unset($source_type_arr[$key]);
//                }
//            }
//            $customer_amount= $CrmCustomerM->customer_select(array_merge($query,$public_yingqi_where, ['source_type'=>['in',$source_type_arr]],['source'=>['not like','%未付款%']]));//39元付款
            $customer_amount= $CrmCustomerM->customer_select(array_merge($query,$public_yingqi_where, ['source'=>$value['media']]));
            if(count($customer_amount)<=0){
                continue;
            }
            $value['refund_rate']='0%';
            $value['wuxiao_rate']='0%';
            $value['youxiao_rate']='0%';
            $value['conversion_rate']= '0%';
            $value['media_amount']= $amount = count($customer_amount);
            $customer_id_arr = array_column($customer_amount,'id');
            $value['refund_amount'] = $CrmCustomerM->customer_count(array_merge(['id'=>['in',$customer_id_arr]],['is_refund'=>1]));
            $conversion_select =$CrmCustomerM->customer_select(array_merge(['id'=>['in',$customer_id_arr]],['public_paytype'=>['in',[2,3]]]));
            $value['conversion'] = count($conversion_select);
            $value['money'] = ($value['conversion']*2980);
            $value['yaoyue_wuxiao'] =$yaoyue_wuxiao =   $CrmCustomerM->customer_count(array_merge(['id'=>['in',$customer_id_arr]],['yaoyue_status'=>['in',[2,5]]]))+$value['refund_amount'];//无效数据=空号或非本人+退款+已接听未加微
//            $customer_become_zhifu = array_column($CrmCustomerM->customer_select(array_merge($query,['source_type'=>['in',$source_type_arr]],['source'=>['like','%未付款%']])),'id');
            $customer_become_zhifu = $CrmCustomerM->customer_count(array_merge($query,$public_yingqi_where,['source'=>$value['media'].'未付款'],['become_zhifu'=>1]));
            $value['youxiao']=($value['media_amount'] - $value['yaoyue_wuxiao'])+$customer_become_zhifu;//已经支付-退款-空号且已支付+转支付(查询时的时间)=有效数
            if($value['youxiao']>0){
                $value['conversion_rate']=(round(($value['conversion']/$value['youxiao']),4)*100).'%';
            }
            if($value['media_amount']>0){
                $value['refund_rate']=(round(($value['refund_amount']/ $value['media_amount']),4)*100).'%';
                $value['wuxiao_rate']=(round(($value['yaoyue_wuxiao']/ $value['media_amount']),4)*100).'%';
                $value['youxiao_rate']=(round(($value['youxiao']/ $value['media_amount']),4)*100).'%';
            }

            unset($value['media_next']);
            array_push($list,$value);
            $media['total_amount']+=$value['media_amount'];
            $media['total_refund_amount']+=$value['refund_amount'];
            $media['total_conversion']+=$value['conversion'];
            $media['wuxiao_amount']+= $yaoyue_wuxiao;
            $media['youxiao_amount'] += $value['youxiao'];
            $media['total_money']+=$value['money'];
        }
        if($media['total_amount']>0){
            $media['total_refund_rate'] = (round(($media['total_refund_amount']/$media['total_amount']),4)*100).'%';
            $media['wuxiao_rate'] = (round(($media['wuxiao_amount']/$media['total_amount']),4)*100).'%';
            $media['youxiao_rate'] = (round(( $media['youxiao_amount']/$media['total_amount']),4)*100).'%';
        }
        if($media['youxiao_amount']>0){
            $media['total_conversion_rate'] = (round(($media['total_conversion']/$media['youxiao_amount']),4)*100).'%';
        }
        $media['list']=$list;
        if(empty($query['endtime']) || empty($query['starttime'])){
            $media['starttime']='';
            $media['endtime']='';
        }else{
            $media['starttime']=$query['starttime'];
            $media['endtime']=$query['endtime'];
        }
        if(!isset($public_yingqi_where['public_yingqi'])){
            $media['public_yingqi']=[];
        }else{
            $media['public_yingqi']=$public_yingqi_where['public_yingqi'][1];
        }
        apiReturn(200,'成功',$media);
    }


    /*
     * 班主任考勤 banzhuren_manage_id
     * formali_yingqi
     * */
    public function banzhuren_customer(){
        $query = input();
        $query['limit'] = isset($query['limit'])?$query['limit']:10;
        $query['page'] = isset($query['page'])?$query['page']:1;
        $customerM = new CrmCustomer();
        $durationsM = new CrmDurations;
        $integralM = new CrmCustomerIntegral();
        $data =  $customerM->formali($query);
        foreach ($data['list'] as $key=>&$value){//,sum(i.) as
            $value['customer_integral']=$integralM->where('phone',$value['phone'])->group('phone')->sum('integral');
            $GoodsNext = (new Goods())->where('id','in',array_column($data['list'],'formali_yingqi'))->with('items')->select();
            foreach ($GoodsNext as $val){
                if($value['formali_yingqi']==$val['id']){
                    $GoodsNextArr = $val['items'];
                    $next_arr = array_column($GoodsNextArr,'id');
                    $durations = $durationsM->where('phone',$value['phone'])->where('course_id','in',$next_arr)->select();
                    $arr = [];
                    $jiazhang=null;
                    $notice=null;
                    foreach ($GoodsNextArr as $k=>&$next){
                        $next['daka']=0;
                        $next['zuoye']=0;
                        $next['status']='未上课';
                        $next['integral_course']=0;
                        $daoke=$work=$expand=$learning_for_practice = $classroom= $unknown=0;
                        if(count($durations)>0){
                            foreach ($durations as $item){
                                if($item['course_id']==$next['id']){
                                    $integral_course=$integralM->where(['phone'=>$item['phone'],'course_id'=>$item['course_id']])->select();
                                    if(count($integral_course)){
                                        foreach ($integral_course as $k=>$course){
                                            $next['integral_course']+=$course->integral;
                                            if($course->type==1){
                                                $daoke=$course['integral'];
                                            }elseif ($course->type==2){
                                                $work=$course['integral'];
                                            }elseif ($course->type==3){
                                                $expand=$course['integral'];
                                            }elseif ($course->type==4){
                                                $learning_for_practice=$course['integral'];
                                            }elseif ($course->type==5){
                                                $classroom=$course['integral'];
                                            }else{
                                                $unknown=$course['integral'];
                                            }
                                        }
                                    }
                                    if($item['status']==0){
                                        $next['status']='未上课';
                                    }elseif ($item['status']==1){
                                        $next['status']='10分钟内';
                                    }elseif ($item['status']==2){
                                        $next['status']='半小时内';
                                    }elseif ($item['status']==3){
                                        $next['status']='1小时内';
                                    }elseif ($item['status']==4){
                                        $next['status']='2小时内';
                                    }elseif ($item['status']==5){
                                        $next['status']='已完课';
                                    }
                                    $next['zuoye'] = $item['zuoye'];
                                    $next['daka'] = $item['daka'];
                                }
                            }
                        }
                        $next['integral_details']=[
                            'daoke'=>$daoke,
                            'work'=>$work,
                            'expand'=>$expand,
                            'learning_for_practice'=>$learning_for_practice,
                            'classroom'=>$classroom,
                            'unknown'=>$unknown
                        ];
                        $jiazhang_title = strpos($next['title'], '家长会');
                        if($jiazhang_title===0 || $jiazhang_title>0){
                            $jiazhang=$next;
                            continue;
                        }
                        $notic_title=strpos($next['title'], '开营须知');
                        if($notic_title===0 || $notic_title>0){
                            $notice=$next;
                            continue;
                        }
                        array_push($arr,$next);
                    }
                    if($jiazhang){
                        array_unshift($arr,$jiazhang);
                    }else{
                        $integral_details=[
                            'daoke'=>0,
                            'work'=>0,
                            'expand'=>0,
                            'learning_for_practice'=>0,
                            'classroom'=>0,
                            'unknown'=>0
                        ];
                        $jia=['status'=>'未上课','daka'=>0,'zuoye'=>0,'title'=>'家长会','integral_details'=>$integral_details];
                        array_unshift($arr,$jia);
                    }
                    if($notice){
                        array_unshift($arr,$notice);
                    }else{
                        $integral_details=[
                            'daoke'=>0,
                            'work'=>0,
                            'expand'=>0,
                            'learning_for_practice'=>0,
                            'classroom'=>0,
                            'unknown'=>0
                        ];
                        $noti=['status'=>'未上课','daka'=>0,'zuoye'=>0,'title'=>'开营须知','integral_details'=>$integral_details];
                        array_unshift($arr,$noti);
                    }
                    $value['item']=$arr;
                }
            }
        }
        apiReturn(200,'成功',$data);
    }

    /*
     * 班主任考勤 banzhuren_manage_id
     * siwei_yingqi
     * */
    public function banzhuren_customer_mind(){
        $query = input();
        $query['limit'] = isset($query['limit'])?$query['limit']:10;
        $query['page'] = isset($query['page'])?$query['page']:1;
        $customerM = new CrmCustomer();
        $durationsM = new CrmDurations;
        $data =  $customerM->siwei($query);
        foreach ($data['list'] as $key=>&$value){
            $GoodsNext = (new Goods())->where('id','in',array_column($data['list'],'siwei_yingqi'))->with('items')->select();
            foreach ($GoodsNext as $val){
                if($value['siwei_yingqi']==$val['id']){
                    $GoodsNextArr = $val['items'];
                    $next_arr = array_column($GoodsNextArr,'id');
                    $durations = $durationsM->where('phone',$value['phone'])->where('course_id','in',$next_arr)->select();
                    $arr = [];
                    $jiazhang=[];
                    foreach ($GoodsNextArr as $k=>&$next){
                        $next['daka']=0;
                        $next['zuoye']=0;
                        $next['status']='未上课';
                        if(count($durations)>0){
                            foreach ($durations as $item){
                                if($item['course_id']==$next['id']){
                                    if($item['status']==0){
                                        $next['status']='未上课';
                                    }elseif ($item['status']==1){
                                        $next['status']='10分钟内';
                                    }elseif ($item['status']==2){
                                        $next['status']='半小时内';
                                    }elseif ($item['status']==3){
                                        $next['status']='1小时内';
                                    }elseif ($item['status']==4){
                                        $next['status']='2小时内';
                                    }elseif ($item['status']==5){
                                        $next['status']='已完课';
                                    }
                                    $next['zuoye'] = $item['zuoye'];
                                    $next['daka'] = $item['daka'];
                                }
                            }
                        }

                        if(strpos($next['title'], '第')===false){
                            array_push($jiazhang,$next);
                            continue;
                        }
                        array_push($arr,$next);
                    }
                    if(count($jiazhang)>0){
                        array_unshift($arr,$jiazhang[0]);
                    }else{
                        $jia=['status'=>'未上课','daka'=>0,'zuoye'=>0,'title'=>'家长会'];
                        array_unshift($arr,$jia);
                    }
                    $value['item']=$arr;
                }
            }
        }
        apiReturn(200,'成功',$data);
    }

    public function goods(){
        apiReturn(200,'ok',(new Goods())->where('search_type','in',[14,15,16])->field('id,title')->select());
    }

    /*
     * 营期班主任统计到课统计
     * siwei_yingqi
     * */
    public function banzhuren_siwei(){
        $query = input();
        $query['limit'] = isset($query['limit'])?$query['limit']:10;
        $query['page'] = isset($query['page'])?$query['page']:1;
        if(empty($query['course_id'])) apiReturn(400,'请选择思维导图');
        $goodsNextM = new GoodsNext();
        $customerM = new CrmCustomer();
        $durationsM = new CrmDurations();
        $data = $customerM->siwei_yingqi($query);
        foreach ($data['list'] as  $key=>&$value){
            $goodsNext = $goodsNextM->where('goods_id',$query['course_id'])->field('id,title,goods_id')->select();
            $item = $value['item'];
            $value['total']=count($item);
            $phone_arr = array_column($item,'phone');
            unset($value['item']);
            $arr= [];
            $jiazhang = [];
            foreach ($goodsNext as $k=>&$val){
                $durations = $durationsM->where('phone','in',$phone_arr)->where('course_id',$val['id'])->group('phone')->select();
                $val['daoke'] = 0 ;
                if(count($durations)<= $value['total']){
                    $val['daoke']=count($durations);
                }
                $val['daoke_rate']='0%';
                if( $val['daoke']>0){
                    $val['daoke_rate']=(round(( $val['daoke']/$value['total']),4)*100).'%';
                }
                $wanke = 0;
                $val['wanke_rate']='0%';
                $zuoye = 0;
                $val['zuoye_rate'] = '0%';
                $daka = 0;
                $val['dake_rate']='0%';
                foreach ($durations as $item){
                    if($item['status']>3){
                        $wanke+=1;
                    }
                    if($item['zuoye']==1){
                        $zuoye+=1;
                    }
                    if($item['daka']==1){
                        $daka+=1;
                    }
                }
                if($val['daoke']>0){
                    $val['wanke_rate']=(round(($wanke/$val['daoke']),4)*100).'%';
                }
                if($value['total']>0){
                    $val['zuoye_rate']=(round(($zuoye/$value['total']),4)*100).'%';
                    $val['dake_rate']=(round(($daka/$value['total']),4)*100).'%';
                }
                if(strpos($val['title'], '第')===false){
                    array_push($jiazhang,$val);
                    continue;
                }
                array_push($arr,$val);
            }
            if(count($jiazhang)>0){
                array_unshift($arr,$jiazhang[0]);
            }else{
                $jia=['daoke'=>0,'title'=>'','daoke_rate'=>'0%','wanke_rate'=>'0%','dake_rate'=>'0%','zuoye_rate'=>'0%','title'=>'家长会'];
                array_unshift($arr,$jia);
            }
            $value['goods_next']=$arr;
        }
        array_multisort($data['list'],SORT_DESC,array_column($data['list'],'total'));
        apiReturn(200,'成功',$data);
    }

    /*
 * 营期班主任统计到课统计
 * formali_yingqi
 * */
    public function banzhuren_yingqi(){
        $query = input();
        $query['limit'] = isset($query['limit'])?$query['limit']:10;
        $query['page'] = isset($query['page'])?$query['page']:1;
        if(empty($query['course_id'])) apiReturn(400,'请选择正式课营期');
        $goodsNextM = new GoodsNext();
        $customerM = new CrmCustomer();
        $durationsM = new CrmDurations();
        $data = $customerM->yingqi($query);
        foreach ($data['list'] as  $key=>&$value){
            $goodsNext = $goodsNextM->where('goods_id',$query['course_id'])->field('id,title,goods_id')->select();
            $item = $value['item'];
            $value['total']=count($item);
            $phone_arr = array_column($item,'phone');
            unset($value['item']);
            $arr= [];
            $jiazhang = null;
            $notice = null;
            foreach ($goodsNext as $k=>&$val){
                $durations = $durationsM->where('phone','in',$phone_arr)->where('course_id',$val['id'])->group('phone')->select();
                $val['daoke'] = 0 ;
                if(count($durations)<= $value['total']){
                    $val['daoke']=count($durations);
                }
                $val['daoke_rate']='0%';
                if( $val['daoke']>0){
                    $val['daoke_rate']=(round(( $val['daoke']/$value['total']),4)*100).'%';
                }
                $wanke = 0;
                $val['wanke_rate']='0%';
                $zuoye = 0;
                $val['zuoye_rate'] = '0%';
                $daka = 0;
                $val['dake_rate']='0%';
                foreach ($durations as $item){
                    if($item['status']>3){
                        $wanke+=1;
                    }
                    if($item['zuoye']==1){
                        $zuoye+=1;
                    }
                    if($item['daka']==1){
                        $daka+=1;
                    }
                }
                if($val['daoke']>0){
                    $val['wanke_rate']=(round(($wanke/$val['daoke']),4)*100).'%';
                }
                if($value['total']>0){
                    $val['zuoye_rate']=(round(($zuoye/$value['total']),4)*100).'%';
                    $val['dake_rate']=(round(($daka/$value['total']),4)*100).'%';
                }
                $jiazhang_title = strpos($val['title'], '家长会');
                if($jiazhang_title===0 || $jiazhang_title>0){
                    $jiazhang=$val;
                    continue;
                }
                $notic_title=strpos($val['title'], '开营须知');
                if($notic_title===0 || $notic_title>0){
                    $notice=$val;
                    continue;
                }
                array_push($arr,$val);
            }
            if($jiazhang){
                array_unshift($arr,$jiazhang);
            }else{
                $jia=['daoke'=>0,'title'=>'','daoke_rate'=>'0%','wanke_rate'=>'0%','dake_rate'=>'0%','zuoye_rate'=>'0%','title'=>'家长会'];
                array_unshift($arr,$jia);
            }
            if($notice){
                array_unshift($arr,$notice);
            }else{
                $noti=['daoke'=>0,'title'=>'','daoke_rate'=>'0%','wanke_rate'=>'0%','dake_rate'=>'0%','zuoye_rate'=>'0%','title'=>'开营须知'];
                array_unshift($arr,$noti);
            }
            $value['goods_next']=$arr;
        }
        array_multisort(array_column($data['list'],'total'),SORT_DESC,$data['list']);
        apiReturn(200,'成功',$data);
    }

    public function channel_count(){
        $query = input();
        $query['limit'] = isset($query['limit'])?$query['limit']:10;
        $query['page'] = isset($query['page'])?$query['page']:1;
        $channelCountM = new CrmChannelCount();
        $res= $channelCountM->count_list($query);
        apiReturn(200,'ok',$res);

    }

    public function jiaowu_data(){
        $query = input();
        if(empty($query['starttime']) || empty($query['endtime'])) {
            $where['date']=['between',[strtotime(date('Y-m-01 00:00:00')),time()]];
        }else{
            $where['date']=['between',[strtotime($query['starttime']),strtotime($query['endtime'])]];
        }
        $group=[];
        if(empty($query['zu_id'])){
            $jiaowu=[15,21,35,36,37];
            foreach ($jiaowu as $group_id){
                $zu = (new CrmFenzu())->fenzu($group_id);
                array_push($zu,$group_id);
                foreach ($zu as $value){
                    array_push($group,$value);
                }
            }
            $manage = (new CrmManage())->alias('m')->join('crm_fenzu f','m.zu_id=f.id')->where('m.zu_id','not in',$group)->field('m.username,m.name,f.name as title,m.zu_id')->select();
        }else{
            $zu = (new CrmFenzu())->fenzu($query['zu_id']);
            array_push($zu,$query['zu_id']);
            foreach ($zu as $value){
                array_push($group,$value);
            }
            $manage = (new CrmManage())->alias('m')->join('crm_fenzu f','m.zu_id=f.id')->where('m.zu_id','in',$group)->field('m.username,m.name,f.name as title,m.zu_id')->select();
        }
        if(!empty($query['course_id'])){
            $where['public_yingqi']=['in',$query['course_id']];
        }
        $username = array_column($manage,'username');
        $where['manage_id']=['in',$username];
        $where['source']=['not like','%未付款%'];
        $customer = (new CrmCustomer())->where($where)->field('manage_id,client_id')->select();
        $stock = array_column($customer,'manage_id');
        $stock = array_count_values($stock);
        if(!empty($query['course_id'])){
            $client_id = (new CrmCustomer())->where('public_yingqi','in',$query['course_id'])->column('client_id');
            $wheres['customer_id']=['in',$client_id];
            $activity_where['customer_id']=['in',$client_id];
        }
        $wheres['manage_id']=$where['manage_id'];
        $wheres['date']=$where['date'];
        $wheres['order_status']=['in',[2,3,4,5]];
        $wheres['total']=['>',1000];
        $orderM = new CrmOrder();
        $order = $orderM->where($wheres)->column('manage_id');
        $order = array_count_values($order);
        $activity_where['date']=$where['date'];
        $activity_where['order_status']=$wheres['order_status'];
        $activity_where['total']=['between',[1000,2500]];
        $activity = $orderM->where($activity_where)->column('manage_id');
        $activity = array_count_values($activity);
        //stock_count：在库数；order_count：总分配数；deal：总成交数；total_money：总成交金额；activity_count：活动成交数；activity_money：活动成交金额；newly_deal_count：新增成交数；newly_deal_money：新增成交金额；deal_rate：转化率
        $total['stock_count']=0;
        $total['order_count']=0;
        $total['deal']=0;
        $total['total_money']=0;
        $total['activity_count']=0;
        $total['activity_money']=0;
        $total['newly_deal_count']=0;
        $total['newly_deal_money']=0;
        $total['deal_rate']='0%';
        $username['rate']=0.000000001;
        foreach ($manage as $key=>&$username){
            $username['stock_count']=0;
            if(isset($stock[$username['username']])){
                $username['stock_count']=$stock[$username['username']];
            }
            $username['deal']=0;
            $username['total_money']=0;
            if(isset($order[$username['username']])){
                $wheres['manage_id']=$username['username'];
                $username['total_money'] = intval($orderM->where($wheres)->sum('total'));
                $username['deal']=$order[$username['username']];
                $username['order_count']=$order[$username['username']]+$username['stock_count'];
            }else{
                $username['order_count']=$username['stock_count'];
            }
            if($username['order_count']==0){
                unset($manage[$key]);
                continue;
            }
            $username['activity_count']=0;
            $username['activity_money'] =0;
            if(isset($activity[$username['username']])){
                $username['activity_count']=$activity[$username['username']];
                $activity_where['manage_id']=$username['username'];
                $username['activity_money'] = $orderM->where($activity_where)->sum('total');
            }
            $username['newly_deal_count']=$username['deal']-$username['activity_count'];
            $username['newly_deal_money']=intval($username['total_money']-$username['activity_money']);
            $username['deal_rate'] ='0%';
            if($username['order_count']>0){
                $username['deal_rate'] =(round(($username['newly_deal_count']/$username['order_count']),4)*100).'%';
                $username['rate'] = round(($username['newly_deal_count']/$username['order_count']),4)*100;
            }
            $total['stock_count']+=$username['stock_count'];
            $total['order_count']+=$username['order_count'];
            $total['deal']+=$username['deal'];
            $total['total_money']+=$username['total_money'];
            $total['activity_count']+=$username['activity_count'];
            $total['activity_money']+=$username['activity_money'];
            $total['newly_deal_count']+=$username['newly_deal_count'];
            $total['newly_deal_money']+=$username['newly_deal_money'];
        }
        $total['total_money'] = intval($total['total_money']);
        if($total['order_count']>0){
            $total['deal_rate']=(round(($total['newly_deal_count']/$total['order_count']),4)*100).'%';
        }
        array_multisort(array_column($manage,'rate'),SORT_DESC,$manage);
        apiReturn(200,'ok',['list'=>$manage,'total'=>$total]);
    }

    public function team_date(){
        $query = input();
        if(empty($query['starttime']) || empty($query['endtime'])) {
            $where['date']=['between',[strtotime(date('Y-m-01 00:00:00')),time()]];
        }else{
            $where['date']=['between',[strtotime($query['starttime']),strtotime($query['endtime'])]];
        }
        $crmFenzuM =  new CrmFenzu();
        $manageM =  new CrmManage();
        $orderM = new CrmOrder();
        $crmCustomerM = new CrmCustomer();
        $crmfenzu = $crmFenzuM->alias('f')->where('f.pid',0)->where('f.id','not in',[15,21,35,36,37])->field('f.id,f.name')->select();
        $total['order_count']=0;
        $total['deal']=0;
        $total['total_money']=0;
        $total['activity_count']=0;
        $total['activity_money']=0;
        $total['newly_deal_count']=0;
        $total['newly_deal_money']=0;
        $total['deal_rate']='0%';
        if(!empty($query['course_id'])){
            $where['public_yingqi']=['in',$query['course_id']];
        }
        foreach ($crmfenzu as &$value){
            $children=$manageM->alias('m')->join('crm_fenzu f','m.zu_id=f.id')->where('m.zu_id','in',$crmFenzuM->where('pid',$value['id'])->column('id'))->group('m.zu_id')->field('m.id,m.name,m.username,m.zu_id,f.name as zu_name')->select();
            $value['order_count']=0;
            $value['deal']=0;
            $value['total_money']=0;
            $value['activity_count']=0;
            $value['activity_money']=0;
            $value['newly_deal_count']=0;
            $value['newly_deal_money']=0;
            $value['deal_rate']=0;
            $manages = $manageM->alias('m')->join('crm_fenzu f','m.zu_id=f.id')->where('m.zu_id',$value['id'])->field('m.id,m.name,m.username,m.zu_id,f.name as zu_name,m.group_id')->select();
            $children = array_merge($children,$manages);
            foreach ($children as $keys=>$manage){
                if(isset($manage['group_id'])){
                    $username=[$manage['username']];
                }else{
                    $username =  $crmFenzuM->zu_username($manage['zu_id']);
                }
                $where['manage_id']=['in',$username];
                $where['source']=['not like','%未付款%'];
                $customer_count = $crmCustomerM->where($where)->count();
                if(!empty($query['course_id'])){
                    $customer = $crmCustomerM->where('public_yingqi','in',$query['course_id'])->column('client_id');
                    $wheres['customer_id']=['in',$customer];
                    $activity_where['customer_id']=['in',$customer];
                }
                $wheres['manage_id']=$where['manage_id'];
                $wheres['date']=$where['date'];
                $wheres['order_status']=['in',[2,3,4,5]];
                $wheres['total']=['>',1000];
                $deal = $orderM->where($wheres)->field('total')->select();
                $manage['name']=$manage['zu_name'].'-'.$manage['name'];
                $manage['deal']=count($deal);
                $manage['total_money'] = array_sum(array_column($deal ,'total'));
                $manage['order_count']= $manage['deal']+$customer_count;
                $activity_where['manage_id']=$where['manage_id'];
                $activity_where['date']=$where['date'];
                $activity_where['order_status']=$wheres['order_status'];
                $activity_where['total']=['between',[1000,2500]];
                $activity = $orderM->where($activity_where)->field('total')->select();
                $manage['activity_count'] = count($activity);
                $manage['activity_money'] = array_sum(array_column($activity,'total'));
                $manage['newly_deal_count']=$manage['deal']-$manage['activity_count'];
                $manage['newly_deal_money']=$manage['total_money']-$manage['activity_money'];
                $manage['deal_rate'] ='0%';
                if($manage['order_count']>0){
                    $manage['deal_rate'] =(round(($manage['newly_deal_count']/$manage['order_count']),4)*100).'%';
                }
                $value['order_count']+=$manage['order_count'];
                $value['deal']+=$manage['deal'];
                $value['total_money']+=$manage['total_money'];
                $value['activity_count']+=$manage['activity_count'];
                $value['activity_money']+=$manage['activity_money'];
                $value['newly_deal_count']+=$manage['newly_deal_count'];
                $value['newly_deal_money']+=$manage['newly_deal_money'];

                $total['order_count']+=$manage['order_count'];
                $total['deal']+=$manage['deal'];
                $total['total_money']+=$manage['total_money'];
                $total['activity_count']+=$manage['activity_count'];
                $total['activity_money']+=$manage['activity_money'];
                $total['newly_deal_count']+=$manage['newly_deal_count'];
                $total['newly_deal_money']+=$manage['newly_deal_money'];
            }
            $value['children'] = $children;
            if($value['order_count']>0){
                $value['deal_rate'] =(round(($value['newly_deal_count']/$value['order_count']),4)*100).'%';
            }
        }
        if($total['order_count']>0){
            $total['deal_rate']=(round(($total['newly_deal_count']/$total['order_count']),4)*100).'%';
        }
        apiReturn(200,'ok',['list'=>$crmfenzu,'total'=>$total]);
    }

    public function supplier(){
        $query = input();
        $query['limit'] = isset($query['limit'])?$query['limit']:10;
        $query['page'] = isset($query['page'])?$query['page']:1;
        if(isset($query['endtime']) && isset($query['starttime'])){
            if($query['starttime']>$query['endtime'])apiReturn(400,'查询开始日期不能大于结束日期');
        }
        if(empty($query['starttime'])|| empty($query['endtime'])){
            unset($query['starttime']);unset($query['endtime']);
        }
        $public_yingqi_where=[];
        if(isset($query['id'])){
            if(!empty($query['id'])){
                $public_yingqi_where['public_yingqi']=['in',$query['id']];
            }
        }else{
            if(empty($query['starttime']) || empty($query['endtime'])){
                $date = $this->date();
                $query['starttime']=$date[0];
                $query['endtime']=$date[1];
            }
        }
        if(isset($query['id'])){
            unset($query['id']);
        }
        $media = (new CrmMedia())->mediaList($query,true);
        $CrmCustomerM = new CrmCustomer;
        $media['total_amount']=0;
        $media['total_conversion'] = 0;
        $media['total_conversion_rate']='0%';
        $media['wuxiao_amount'] = 0;
        $media['youxiao_amount'] = 0;
        $media['total_money']=0;
        $media['total_settlement']=0;
        $media['total_cpa']=0;
        $media['total_settlement_real']=0;
        $list = [];
        foreach ($media['list'] as $key=>&$value){
//            $source_type_arr = array_column($value['media_next'],'type');
//            foreach ($source_type_arr as $key=>$type){
//                if($type===''){
//                    unset($source_type_arr[$key]);
//                }
//            }
            $customer_amount= $CrmCustomerM->customer_select(array_merge($query,$public_yingqi_where, ['source'=>$value['media']]));//39元付款
            if(count($customer_amount)<=0){
                continue;
            }
            $value['conversion_rate']= '0%';
            $value['settlement']=0;
            $value['cpa']=0;
            $value['settlement_real']=0;
            $value['media_amount']= $amount = count($customer_amount);
            $customer_id_arr = array_column($customer_amount,'id');
            $conversion_select =$CrmCustomerM->customer_select(array_merge(['id'=>['in',$customer_id_arr]],['public_paytype'=>['in',[2,3]]]));
            $value['conversion'] = count($conversion_select);
            $value['money'] = ($value['conversion']*2980);
            if($value['youxiao_status']===null || $value['youxiao_status']===''){
                $value['youxiao']='未设置有效数据';//$value['media_amount'] - $value['yaoyue_wuxiao'];
                $value['yaoyue_wuxiao'] =$value['media_amount']-0;
                $value['youxiao_s']=0;
            }else{
                $youxiao_status =  explode(',',$value['youxiao_status']);
                $value['youxiao']=$CrmCustomerM->customer_count(array_merge(['id'=>['in',$customer_id_arr]],['yaoyue_status'=>['in',$youxiao_status]]));
                $value['youxiao_s']=$value['youxiao'];
                $value['yaoyue_wuxiao'] =$value['media_amount']-$value['youxiao_s'];
            }
            $settlement=($value['money']*($value['ticheng_rate']/100));
            if($value['price_jiesuan']){
                $settlement+=$value['price']*$value['youxiao_s'];
                $value['cpa']=round(($value['jiesuan_price']+$value['price'])*$value['youxiao_s'],2);
            }else{
                $value['cpa']=round(($value['jiesuan_price']*$value['youxiao_s']),2);
            }
            $media['total_cpa']+=$value['cpa'];
            if($value['youxiao_s']>0){
                $value['conversion_rate']=(round(($value['conversion']/$value['youxiao_s']),4)*100).'%';
            }
            $value['settlement']=round($settlement,2);
            $media['total_settlement']+=$value['settlement'];
            if($value['settlement']<$value['cpa']){
                $value['settlement_real']=$value['cpa'];
            }else{
                $value['settlement_real']=$value['settlement'];
            }
            $media['total_settlement_real']+=$value['settlement_real'];
            unset($value['media_next']);
            array_push($list,$value);
            $media['total_amount']+=$value['media_amount'];
            $media['total_conversion']+=$value['conversion'];
            $media['wuxiao_amount']+= $value['yaoyue_wuxiao'];
            $media['youxiao_amount'] += $value['youxiao_s'];
            $media['total_money']+=$value['money'];
        }
        $media['total_settlement']=round($media['total_settlement'],2);
        $media['total_cpa']=round($media['total_cpa'],2);
        $media['total_settlement_real']=round($media['total_settlement_real'],2);
        if($media['youxiao_amount']>0){
            $media['total_conversion_rate'] = (round(($media['total_conversion']/$media['youxiao_amount']),4)*100).'%';
        }
        $media['list']=$list;
        if(empty($query['endtime']) || empty($query['starttime'])){
            $media['starttime']='';
            $media['endtime']='';
        }else{
            $media['starttime']=$query['starttime'];
            $media['endtime']=$query['endtime'];
        }
        apiReturn(200,'成功',$media);
    }

    private function date(){
        $date=date('Y-m-d', time());
        $first=1;
        $w=date('w',strtotime($date));
        $now_start=date('Y-m-d',strtotime("$date -".($w ? $w - $first : 6).' days'));
        $end_expire_thursday = strtotime($now_start)-(86400*3);
        $start_expire_thursday =$end_expire_thursday-(86400*7);
        return[date('Y-m-d H:i:s',$start_expire_thursday),date('Y-m-d H:i:s',$end_expire_thursday-1)];

    }

    //渠道公开课报表
    public function open_class(){
        $query = input();
        $query['limit'] = isset($query['limit'])?$query['limit']:10;
        $query['page'] = isset($query['page'])?$query['page']:1;
        $public_yingqi_where=[];
        if(isset($query['id'])){
            if(!empty($query['id'])){
                $public_yingqi_where['public_yingqi']=['in',$query['id']];
            }
            unset($query['id']);
        }else{
            $goods=(new Goods())->where('search_type','in',[1,2,3])->where('time','<',time())->page(1)->limit(2)->order('time desc')->select();
            $public_yingqi_where['public_yingqi']=['in',[$goods[0]['id'],$goods[1]['id']]];
        }
        $media = (new CrmMedia())->mediaList($query);
        $CrmCustomerM = new CrmCustomer;
        $crmDurationsM = new CrmDurations;
        $course = (new GoodsNext())->where(['goods_id'=>$public_yingqi_where['public_yingqi']])->select();
        $first_day=[];
        $the_next_day=[];
        foreach ($course as $itme){
            if (strpos($itme['title'], '第一天') !== false) {
                array_push($first_day,$itme['id']);
            }else{
                array_push($the_next_day,$itme['id']);
            }
        }
        $course = array_column($course,'id');
        $list = [];
        $media['first_day_not_attending_class_sum']=0;
        $media['first_day_ten_minutes_sum']=0;
        $media['first_day_half_an_hour_sum']=0;
        $media['first_day_an_hour_sum']=0;
        $media['first_day_two_hours_sum']=0;
        $media['first_day_complete_sum']=0;
        $media['the_next_day_not_attending_class_sum']=0;
        $media['the_next_day_ten_minutes_sum']=0;
        $media['the_next_day_half_an_hour_sum']=0;
        $media['the_next_day_an_hour_sum']=0;
        $media['the_next_day_two_hours_sum']=0;
        $media['the_next_day_complete_sum']=0;
        $media['day_total']=0;
        $media['first_day_complete_rate']='0%';
        $media['the_next_day_complete_rate']='0%';
        foreach ($media['list'] as $key=>$value){
            unset($value['media_next']);
            $customer_amount= $CrmCustomerM->customer_select(array_merge($query,$public_yingqi_where, ['source'=>$value['media']]));
            $customer_phone_arr = array_column($customer_amount,'phone');
            $durations = $crmDurationsM->where('phone','in',$customer_phone_arr)->where('course_id','in',$course)->select();
            if(count($customer_amount)<=0){
                unset($media['list'][$key]);
                continue;
            }
            $value['first_day_ke']=[
                'ten_minutes'=>0,
                'half_an_hour'=>0,
                'an_hour'=>0,
                'two_hours'=>0,
                'complete'=>0
            ];
            $value['the_next_day_ke']=[
                'ten_minutes'=>0,
                'half_an_hour'=>0,
                'an_hour'=>0,
                'two_hours'=>0,
                'complete'=>0
            ];
            $value['first_day_complete_rate']='0%';
            $value['the_next_day_complete_rate']='0%';
            //dump($crmDurationsM->getLastSql());die;
            foreach ($durations as $v){
                if(in_array($v['course_id'],$first_day)){
                    $value['first_day_ke'] = $this->open_class_ke($v,$value['first_day_ke']);
                }
                if(in_array($v['course_id'],$the_next_day)){
                    $value['the_next_day_ke'] = $this->open_class_ke($v,$value['the_next_day_ke']);
                }
            }
            $total_people = count($customer_amount);
            $first_day_ke_not_attending_class =$total_people-($value['first_day_ke']['ten_minutes']+$value['first_day_ke']['half_an_hour']+$value['first_day_ke']['an_hour']+$value['first_day_ke']['two_hours']+$value['first_day_ke']['complete']);
            $the_next_day_not_attending_class =$total_people-($value['the_next_day_ke']['ten_minutes']+$value['the_next_day_ke']['half_an_hour']+$value['the_next_day_ke']['an_hour']+$value['the_next_day_ke']['two_hours']+$value['the_next_day_ke']['complete']);
            $value['day_total']=$total_people;
            $value['first_day_ke_not_attending_class']=$first_day_ke_not_attending_class;
            $value['the_next_day_ke_not_attending_class']=$the_next_day_not_attending_class;
            if($value['day_total']>0){
                $value['first_day_complete_rate']= (round(($value['first_day_ke']['complete']/$value['day_total']),4)*100).'%';
            }
            if($value['day_total']>0){
                $value['the_next_day_complete_rate']=(round(($value['the_next_day_ke']['complete']/$value['day_total']),4)*100).'%';
            }
            $media['first_day_not_attending_class_sum']+=$value['first_day_ke_not_attending_class'];
            $media['first_day_ten_minutes_sum']+=$value['first_day_ke']['ten_minutes'];
            $media['first_day_half_an_hour_sum']+=$value['first_day_ke']['half_an_hour'];
            $media['first_day_an_hour_sum']+=$value['first_day_ke']['an_hour'];
            $media['first_day_two_hours_sum']+=$value['first_day_ke']['two_hours'];
            $media['first_day_complete_sum']+=$value['first_day_ke']['complete'];
            $media['the_next_day_not_attending_class_sum']+= $value['the_next_day_ke_not_attending_class'];
            $media['the_next_day_ten_minutes_sum']+=$value['the_next_day_ke']['ten_minutes'];
            $media['the_next_day_half_an_hour_sum']+=$value['the_next_day_ke']['half_an_hour'];
            $media['the_next_day_an_hour_sum']+=$value['the_next_day_ke']['an_hour'];
            $media['the_next_day_two_hours_sum']+=$value['the_next_day_ke']['two_hours'];
            $media['the_next_day_complete_sum']+=$value['the_next_day_ke']['complete'];
            $media['day_total']+= $value['day_total'];
            $value['sort']=$value['first_day_ke']['two_hours'];
            array_push($list,$value);
        }
        if($media['day_total']>0){
            $media['first_day_complete_rate'] = (round(($media['first_day_complete_sum']/$media['day_total']),4)*100).'%';
        }
        if($media['day_total']>0){
            $media['the_next_day_complete_rate'] = (round(($media['the_next_day_complete_sum']/$media['day_total']),4)*100).'%';
        }
        array_multisort(array_column($list,'sort'),SORT_DESC,$list);
        $media['list'] = $list;
        $media['public_yingqi']=$public_yingqi_where['public_yingqi'][1];
        apiReturn(200,'成功',$media);
    }

    private function open_class_ke($durations,$ke){
        if($durations['status']==1){
            $ke['ten_minutes']++;
        }elseif ($durations['status']==2){
            $ke['half_an_hour']++;
        }elseif ($durations['status']==3){
            $ke['an_hour']++;
        }elseif ($durations['status']==4){
            $ke['two_hours']++;
        }elseif ($durations['status']==5){
            $ke['complete']++;
        }
        return $ke;
    }

    

}